﻿using System;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;

using ZhangBY.Config;
using ZhangBY.Common;
using ZhangBY.Entity;

namespace ZhangBY.Data.SqlServer
{
    public partial class DataProvider : IDataProvider
    {        

        #region 企业留言
        #region 留言
        /// <summary>
        /// 发布留言
        /// </summary>
        /// <returns></returns>
        public int InsertComment(CommentInfo commentInfo)
        {
            DbParameter[] parms = {
                                      DbHelper.MakeInParam("@userid",(DbType)SqlDbType.NVarChar,60,commentInfo.UserUid),
                                      DbHelper.MakeInParam("@commentid",(DbType)SqlDbType.NVarChar,60,commentInfo.CommentId),
                                      DbHelper.MakeInParam("@nickname",(DbType)SqlDbType.NChar,20,commentInfo.NickName),
                                      DbHelper.MakeInParam("@title",(DbType)SqlDbType.NVarChar,100,commentInfo.Title),
                                      DbHelper.MakeInParam("@ccontent",(DbType)SqlDbType.NVarChar,500,commentInfo.CContent),
                                      DbHelper.MakeInParam("@reply",(DbType)SqlDbType.Bit,5,commentInfo.Reply),
                                      DbHelper.MakeInParam("@audit",(DbType)SqlDbType.Bit,5,commentInfo.Audit),
                                      DbHelper.MakeInParam("@date",(DbType)SqlDbType.DateTime,commentInfo.Date.Length,commentInfo.Date)
                                  };
            System.Text.StringBuilder commantText = new System.Text.StringBuilder();
            commantText.AppendFormat("INSERT INTO [ZhangBY].[dbo].[{0}comment] ", BaseConfigs.GetTablePrefix);
            commantText.Append("([userid],[commentid],[nickname],[title],[ccontent] ,[reply],[audit],[date])");
            commantText.Append("VALUES(@userid,@commentid,@nickname,@title,@ccontent,@reply,@audit,@date)");
            return DbHelper.ExecuteNonQuery(CommandType.Text, commantText.ToString(), parms);
        }
        /// <summary>
        /// 根据用户ID和commentid更新供应信息
        /// </summary>
        /// <param name="commentInfo"></param>
        /// <returns></returns>
        public int UpdateCommentByCommentIdAndUserID(CommentInfo commentInfo)
        {
            DbParameter[] parms = {
                                      DbHelper.MakeInParam("@userid",(DbType)SqlDbType.NVarChar,60,commentInfo.UserUid),
                                      DbHelper.MakeInParam("@commentid",(DbType)SqlDbType.NVarChar,60,commentInfo.CommentId),
                                      DbHelper.MakeInParam("@nickname",(DbType)SqlDbType.NChar,20,commentInfo.NickName),
                                      DbHelper.MakeInParam("@title",(DbType)SqlDbType.NVarChar,100,commentInfo.Title),
                                      DbHelper.MakeInParam("@ccontent",(DbType)SqlDbType.NVarChar,500,commentInfo.CContent),
                                      DbHelper.MakeInParam("@reply",(DbType)SqlDbType.Bit,5,commentInfo.Reply),
                                      DbHelper.MakeInParam("@audit",(DbType)SqlDbType.Bit,5,commentInfo.Audit),
                                      DbHelper.MakeInParam("@date",(DbType)SqlDbType.DateTime,commentInfo.Date.Length,commentInfo.Date)
                                  };
            System.Text.StringBuilder commantText = new System.Text.StringBuilder();
            commantText.AppendFormat("UPDATE [ZhangBY].[dbo].[{0}comment] ", BaseConfigs.GetTablePrefix);
            commantText.Append("SET,[nickname]=@nickname,[title]=@title,[ccontent]=@ccontent ,[reply]=@reply,[audit]=@audit,[date]=@date");
            commantText.Append(" WHERE [commentid]=@commentid AND[userid]=@userid");
            return DbHelper.ExecuteNonQuery(CommandType.Text, commantText.ToString(), parms);
        }
        
        /// <summary>
        /// 根据commentid更新留言信息
        /// </summary>
        /// <param name="commentInfo"></param>
        /// <returns></returns>
        public int UpdateCommentByCommentId(CommentInfo commentInfo)
        {
            DbParameter[] parms = {
                                      DbHelper.MakeInParam("@commentid",(DbType)SqlDbType.NVarChar,60,commentInfo.CommentId),
                                      DbHelper.MakeInParam("@nickname",(DbType)SqlDbType.NChar,20,commentInfo.NickName),
                                      DbHelper.MakeInParam("@title",(DbType)SqlDbType.NVarChar,100,commentInfo.Title),
                                      DbHelper.MakeInParam("@ccontent",(DbType)SqlDbType.NVarChar,500,commentInfo.CContent),
                                      DbHelper.MakeInParam("@reply",(DbType)SqlDbType.Bit,5,commentInfo.Reply),                                      
                                      DbHelper.MakeInParam("@audit",(DbType)SqlDbType.Bit,5,commentInfo.Audit),
                                      DbHelper.MakeInParam("@date",(DbType)SqlDbType.DateTime,commentInfo.Date.Length,commentInfo.Date)
                                  };
            System.Text.StringBuilder commantText = new System.Text.StringBuilder();
            commantText.AppendFormat("UPDATE [ZhangBY].[dbo].[{0}comment] ", BaseConfigs.GetTablePrefix);
            commantText.Append("SET,[nickname]=@nickname,[title]=@title,[ccontent]=@ccontent ,[reply]=@reply,[audit]=@audit,[date]=@date");
            commantText.Append(" WHERE [commentid]=@commentid ");
            return DbHelper.ExecuteNonQuery(CommandType.Text, commantText.ToString(), parms);
        }

        /// <summary>
        /// 根据USERID和commentid查找留言信息
        /// </summary>
        /// <param name="userid"></param>
        /// <param name="commentid"></param>
        /// <returns></returns>
        public DataTable GetCommentByCommentIdAndUserId(string userid,string commentid)
        {
            DbParameter[] parms = {
                                      DbHelper.MakeInParam("@userid",(DbType)SqlDbType.NVarChar,60,userid),
                                      DbHelper.MakeInParam("@commentid",(DbType)SqlDbType.NVarChar,60,commentid)
                                  };
            string commandText = string.Format("SELECT * FROM [dbo].[{0}comment] WHERE [userid]=@userid AND [commentid]=@commentid", BaseConfigs.GetTablePrefix);
            return DbHelper.ExecuteDataset(CommandType.Text,commandText,parms).Tables[0];
        }
        /// <summary>
        /// 根据PROID查找留言信息
        /// </summary>
        /// <param name="commentid"></param>
        /// <returns></returns>
        public DataTable GetCommentByCommentId(string commentid)
        {
            DbParameter parms = DbHelper.MakeInParam("@commentid",(DbType)SqlDbType.NVarChar,60,commentid);
            string commandText = string.Format("SELECT * FROM [dbo].[{0}comment] WHERE [commentid]=@commentid", BaseConfigs.GetTablePrefix);
            return DbHelper.ExecuteDataset(CommandType.Text, commandText, parms).Tables[0];
        }
        /// <summary>
        /// 根据USERID读取所有留言数据行
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public IDataReader GetCommentByUserId(string userid)
        {
            DbParameter parms = DbHelper.MakeInParam("@userid", (DbType)SqlDbType.NVarChar, 60, userid);
            string commandText = string.Format("SELECT * FROM [dbo].[{0}comment] WHERE [userid]=@userid ORDER BY [date] DESC", BaseConfigs.GetTablePrefix);
            return DbHelper.ExecuteReader(CommandType.Text, commandText, parms);
        }

       

        /// <summary>
        /// 根据USERID和PROID删除留言信息
        /// </summary>
        /// <param name="userid"></param>
        /// <param name="commentid"></param>
        /// <returns></returns>
        public int DelCommentByUserIdAndCommentId(string userid, string commentid)
        {
            int i = 0;
            SqlConnection conn = new SqlConnection(DbHelper.ConnectionString);
            conn.Open();
            using (System.Data.SqlClient.SqlTransaction trans = conn.BeginTransaction())
            {
                try
                {
                    DbParameter[] parms1 = {
                                      DbHelper.MakeInParam("@userid",(DbType)SqlDbType.NVarChar,60,userid),
                                      DbHelper.MakeInParam("@commentid",(DbType)SqlDbType.NVarChar,60,commentid)
                                   };
                    string commandText1 = string.Format("DELETE FROM [dbo].[{0}comment]  WHERE [userid]=@userid AND [commentid]=@commentid", BaseConfigs.GetTablePrefix);

                    if (DbHelper.ExecuteNonQuery(CommandType.Text, commandText1, parms1) > 0)
                    {
                        DbParameter parms2 = DbHelper.MakeInParam("@commentid", (DbType)SqlDbType.NVarChar, 60, commentid);
                        string commandText2 = string.Format("DELETE FROM [dbo].[{0}commentre] WHERE [commentid]=@commentid", BaseConfigs.GetTablePrefix);
                        DbHelper.ExecuteNonQuery(trans, CommandType.Text, commandText2, parms2);
                        i = 1;
                    }
                    trans.Commit();                    
                }
                catch (Exception ex)
                {
                    trans.Rollback();
                    throw ex;
                }
            }
            conn.Close();
            return i;
        }
        /// <summary>
        /// 根据PROID删除留言信息
        /// </summary>
        /// <param name="commentid"></param>
        /// <returns></returns>
        public int DelCommentByCommentId(string commentid)
        {

            int i = 0;
            SqlConnection conn = new SqlConnection(DbHelper.ConnectionString);
            conn.Open();
            using (System.Data.SqlClient.SqlTransaction trans = conn.BeginTransaction())
            {
                try
                {
                    DbParameter[] parms1 = {
                                      DbHelper.MakeInParam("@commentid",(DbType)SqlDbType.NVarChar,60,commentid)
                                   };
                    string commandText1 = string.Format("DELETE FROM [dbo].[{0}comment]  WHERE  AND [commentid]=@commentid", BaseConfigs.GetTablePrefix);

                    if (DbHelper.ExecuteNonQuery(CommandType.Text, commandText1, parms1) > 0)
                    {
                        DbParameter parms2 = DbHelper.MakeInParam("@commentid", (DbType)SqlDbType.NVarChar, 60, commentid);
                        string commandText2 = string.Format("DELETE FROM [dbo].[{0}commentre] WHERE [commentid]=@commentid", BaseConfigs.GetTablePrefix);
                        DbHelper.ExecuteNonQuery(trans, CommandType.Text, commandText2, parms2);
                        i = 1;
                    }
                    trans.Commit(); 
                }
                catch (Exception ex)
                {
                    trans.Rollback();
                    throw ex;
                }
            }
            conn.Close();
            return i;


        }
        /// <summary>
        /// 根据用户ID取得留言产品总行数
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public int GetCountCommentByUserId(string userid)
        {
            DbParameter parms = DbHelper.MakeInParam("@userid", (DbType)SqlDbType.NVarChar, 60, userid);
            string commandText = string.Format("SELECT COUNT(commentid) FROM [dbo].[{0}comment] WHERE [userid]=@userid",BaseConfigs.GetTablePrefix);
            return Utils.StrToInt(DbHelper.ExecuteScalar(CommandType.Text, commandText, parms), 0);
        }
        #endregion

        #region 留言回复
        /// <summary>
        /// 回复留言
        /// </summary>
        /// <param name="commentInfo"></param>
        /// <returns></returns>
        public int InsertReComment(CommentReInfo commentReInfo)
        {
            DbParameter[] parms = {
                                      DbHelper.MakeInParam("@reid",(DbType)SqlDbType.NVarChar,60,commentReInfo.ReId),
                                      DbHelper.MakeInParam("@commentid",(DbType)SqlDbType.NVarChar,60,commentReInfo.CommentId),
                                      DbHelper.MakeInParam("@nickname",(DbType)SqlDbType.NChar,20,commentReInfo.NickName),
                                      DbHelper.MakeInParam("@rcontent",(DbType)SqlDbType.NVarChar,500,commentReInfo.RContent),
                                      DbHelper.MakeInParam("@date",(DbType)SqlDbType.DateTime,commentReInfo.Date.Length,commentReInfo.Date)
                                  };
            System.Text.StringBuilder commantText = new System.Text.StringBuilder();
            commantText.AppendFormat("INSERT INTO [ZhangBY].[dbo].[{0}commentre] ", BaseConfigs.GetTablePrefix);
            commantText.Append("([reid],[commentid],[nickname],[rcontent] ,[date])");
            commantText.Append("VALUES(@reid,@commentid,@nickname,@rcontent,@date)");
            return DbHelper.ExecuteNonQuery(CommandType.Text, commantText.ToString(), parms);
        }

        /// <summary>
        /// 取得指定ID的所有回复内容
        /// </summary>
        /// <param name="commentid"></param>
        /// <returns></returns>
        public DataTable GetReComment(string commentid)
        {
            DbParameter parms = DbHelper.MakeInParam("@commentid", (DbType)SqlDbType.NVarChar, 60, commentid);
            string commandText = string.Format("SELECT * FROM [dbo].[{0}commentre] WHERE [commentid]=@commentid ORDER BY [date] ASC", BaseConfigs.GetTablePrefix);
            return DbHelper.ExecuteDataset(CommandType.Text, commandText, parms).Tables[0];
        }

        /// <summary>
        /// 删除回复
        /// </summary>
        /// <param name="reid"></param>
        /// <returns></returns>
        public int DelReCommentByCommentIdAndReId(string commentid, string reid)
        {
            DbParameter[] parms = {
                                      DbHelper.MakeInParam("@reid", (DbType)SqlDbType.NVarChar, 60, reid),
                                      DbHelper.MakeInParam("@commentid", (DbType)SqlDbType.NVarChar, 60, commentid)
                                  };
            string commandText = string.Format("DELETE FROM [dbo].[{0}commentre] WHERE [reid]=@reid AND [commentid]=@commentid", BaseConfigs.GetTablePrefix);
            return DbHelper.ExecuteNonQuery(CommandType.Text, commandText, parms);
        }
        #endregion

        #endregion

    }
}